package com.iswoak.netctoss.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.iswoak.netctoss.dao.FeeInfoDao;
import com.iswoak.netctoss.entity.FeeInfo;
import com.iswoak.netctoss.util.DBUtil;

/**
 * @version: 1.0
 * @author: fxz
 * @Description:
 * @date: 2019年7月6日 下午2:50:34
 */
public class FeeInfoDaoImpl implements FeeInfoDao {

	//分页显示所查询到数据
		public List<FeeInfo> page(Integer currPage, Integer pageSize) {
			Connection conn=DBUtil.getConnection();
			Statement stmt=null;
			List<FeeInfo> list=null;
			FeeInfo feeInfo = null;
			String sql = "select * "
					+ "FROM cost ";
			//处理查询条件 
			sql+=" LIMIT "+currPage+","+pageSize;
			try {
				stmt=conn.createStatement();
				ResultSet rs=stmt.executeQuery(sql);
				//创建返回结果集
				list=new ArrayList<FeeInfo>();
				//处理结果集idcardNo
				while(rs.next()){
					feeInfo = new FeeInfo();
					int costId = rs.getInt("cost_id");
					String name = rs.getString("name");
					int baseDuration = rs.getInt("base_duration");
					Double unitCost = rs.getDouble("unit_cost");
					Double baseCost = rs.getDouble("base_cost");
					String desr = rs.getString("desr");
					int status = rs.getInt("status");
					String startTime = rs.getString("startime");
					String creatTime = rs.getString("creatime");
					int costType = rs.getInt("cost_type");
					feeInfo.setCostId(costId);
					feeInfo.setBaseCost(baseCost);
					feeInfo.setBaseDuration(baseDuration);
					feeInfo.setCostType(costType);
					feeInfo.setCreatTime(creatTime);
					feeInfo.setStartTime(startTime);
					feeInfo.setDesr(desr);
					feeInfo.setUnitCost(unitCost);
					feeInfo.setName(name);
					feeInfo.setStatus(status);
					list.add(feeInfo);
				}
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(stmt!=null){
					try {
						stmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				DBUtil.closeConnection(conn);
			}
			return list;
		}

		public Integer count() {
			Connection conn=DBUtil.getConnection();
			Statement stmt=null;
			int count=0;
			String sql = "select count(DISTINCT cost_id)  "
					+ "FROM cost ";
			try {
				stmt=conn.createStatement();
				ResultSet rs=stmt.executeQuery(sql);
				while(rs.next()){
					count=rs.getInt(1);
				}
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(stmt!=null){
					try {
						stmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				DBUtil.closeConnection(conn);
			}
			return count;
		}
		public FeeInfo findByDetailId(int costId) {
			Connection conn=DBUtil.getConnection();
			PreparedStatement ps=null;
			FeeInfo feeInfo = null;
			String sql = "SELECT * "
					+ "FROM cost "
					+ "where cost_id = ?";
			try {
				ps=conn.prepareStatement(sql);
				ps.setInt(1, costId);
				ResultSet rs=ps.executeQuery();
				while(rs.next()){
					feeInfo = new FeeInfo();
					costId = rs.getInt("cost_id");
					String name = rs.getString("name");
					int baseDuration = rs.getInt("base_duration");
					Double unitCost = rs.getDouble("unit_cost");
					Double baseCost = rs.getDouble("base_cost");
					String desr = rs.getString("desr");
					int status = rs.getInt("status");
					String startTime = rs.getString("startime");
					String creatTime = rs.getString("creatime");
					int costType = rs.getInt("cost_type");
					feeInfo.setCostId(costId);
					feeInfo.setBaseCost(baseCost);
					feeInfo.setBaseDuration(baseDuration);
					feeInfo.setCostType(costType);
					feeInfo.setCreatTime(creatTime);
					feeInfo.setStartTime(startTime);
					feeInfo.setDesr(desr);
					feeInfo.setUnitCost(unitCost);
					feeInfo.setName(name);
					feeInfo.setStatus(status);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//将请求的业务id传入sql语句
			return feeInfo;
		}
		
		//删除
		public int del(Integer costId) {

			Connection conn=DBUtil.getConnection();
			Statement stmt=null;
			int rs = 0;
			String sql = "delete from cost where cost_id = " + costId;
			try {
				stmt=conn.createStatement();
				rs = stmt.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(stmt!=null){
					try {
						stmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				DBUtil.closeConnection(conn);
			}
			return rs;
		}
		
		//修改状态为开通
		public int updateStatus(Integer costId) {
			Connection conn=DBUtil.getConnection();
			Statement stmt=null;
			int rs = 0;
			Date date = new Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String now = sdf.format(date);
			String sql = "update cost set status = '0', startime = '" +now + "'"
						+ "where cost_id = " + costId;
			try {
				stmt=conn.createStatement();
				rs = stmt.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(stmt!=null){
					try {
						stmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				DBUtil.closeConnection(conn);
			}
			return rs;
		}
		
		
	// 排序分页显示所查询到数据
	public List<FeeInfo> sortList(String column, String flag,Integer currPage, Integer pageSize) {
		Connection conn = DBUtil.getConnection();
		Statement stmt = null;
		List<FeeInfo> list = null;
		FeeInfo feeInfo = null;
		String sql = "select * " + "FROM cost ";
		// 处理查询条件
		sql += " order by " + column + " " + flag;
		sql += " LIMIT " + currPage + "," + pageSize;
		System.out.println("排序：" + sql);
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			// 创建返回结果集
			list = new ArrayList<FeeInfo>();
			// 处理结果集idcardNo
			while (rs.next()) {
				feeInfo = new FeeInfo();
				int costId = rs.getInt("cost_id");
				String name = rs.getString("name");
				int baseDuration = rs.getInt("base_duration");
				Double unitCost = rs.getDouble("unit_cost");
				Double baseCost = rs.getDouble("base_cost");
				String desr = rs.getString("desr");
				int status = rs.getInt("status");
				String startTime = rs.getString("startime");
				String creatTime = rs.getString("creatime");
				int costType = rs.getInt("cost_type");
				feeInfo.setCostId(costId);
				feeInfo.setBaseCost(baseCost);
				feeInfo.setBaseDuration(baseDuration);
				feeInfo.setCostType(costType);
				feeInfo.setCreatTime(creatTime);
				feeInfo.setStartTime(startTime);
				feeInfo.setDesr(desr);
				feeInfo.setUnitCost(unitCost);
				feeInfo.setName(name);
				feeInfo.setStatus(status);
				list.add(feeInfo);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			DBUtil.closeConnection(conn);
		}
		return list;
	}
		
}
